/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.loli.hernandez.modelo;

import com.loli.hernandez.modelo.beans.DatabaseTable;
import com.loli.hernandez.modelo.beans.TableColumn;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author nestor
 */
public class StoredProcedureGenerator {

    private final DataManager dataManager;
    private String insertProcName = "_Registrar_";
    private String selectProcName = "_Listar_";
    private String detailsProcName = "_por_Codigo";
    private String updateProcName = "_Actualizar_";
    private String deleteProcName = "_Borrar_";
    private String procedurePrefix = "USP";
    //TODO: to be implemeted
    private boolean shortSyntax = false;

    public StoredProcedureGenerator(DataManager dataManager) {
        this.dataManager = dataManager;

    }
    private ArrayList<TableColumn> tableColumnList = new ArrayList<TableColumn>();
    private boolean columnListEmpty = true;

    public void setTableColumnList(ArrayList<TableColumn> tableColumnList) {
        this.tableColumnList = tableColumnList;
        columnListEmpty = false;
    }

    private void fillTableColumnList(DatabaseTable table) throws SQLException {
        if (columnListEmpty) {
            tableColumnList = dataManager.getColumnsFromTable(table);
            columnListEmpty = false;
        } else {

            // Filtrar las columnas que han sido marcadas para poder generar código
            ArrayList<TableColumn> columnaSeleccionadas = new ArrayList<TableColumn>();
            for (TableColumn col : tableColumnList) {
                if (col.isAllowedForCodeGeneration()) {
                    columnaSeleccionadas.add(col);
                }
            }

            tableColumnList = columnaSeleccionadas;
        }
    }

    private String getTableColumns() {

        String result = "";
        int cantCampos = 0;
        for (TableColumn col : tableColumnList) {

            cantCampos++;
            result += "`" + col.getField() + "`" + ", " + darSaltoLinea(cantCampos);

        }

        int i = result.lastIndexOf(',');
        if (i >= 0) {
            result = result.substring(0, i);
        }

        return result;
    }

    //Este método ayuda a formatear el procedimiento 
    //Sin este método INSERT INTO `customers`(`CustomerID`, `CompanyName`, `ContactName`, `ContactTitle`,`Address`, `City`, `Region`, `PostalCode`, `Country`, `Phone`, `..)

    /*
    Con este método
     * INSERT INTO `customers`(`CustomerID`, `CompanyName`, `ContactName`, 
     * `ContactTitle`,`Address`, `City`, 
     * `Region`, `PostalCode`, `Country`, 
     * `Phone`, `Fax`)
    VALUES (in_CustomerID, in_CompanyName, in_ContactName, 
     * in_ContactTitle,in_Address, in_City, in_Region, 
     * in_PostalCode, in_Country, in_Phone, in_Fax);
     */
    private String darSaltoLinea(int cantCampos) {
        int LIMITE_CAMPOS_PARA_SALTO = 4;
        if ((cantCampos % LIMITE_CAMPOS_PARA_SALTO) == 0) {
            return "\n";
        }
        return "";
    }

    private String getPrimaryKeysAsStoredProcParams() {
        String result = "";
        for (TableColumn col : tableColumnList) {
            if (col.isPrimaryKey()) {
                result += "\nIN p_" + col.getField() + " " + col.getType() + ", ";
            }

        }
        int i = result.lastIndexOf(',');
        if (i >= 0) {
            result = result.substring(0, i);
        }
        return result;
    }

    private String getStoredProcParameters() {

        String result = "";

        for (TableColumn col : tableColumnList) {
            result += "\nIN p_" + col.getField() + " " + col.getType() + ", ";

        }
        int i = result.lastIndexOf(',');
        if (i >= 0) {
            result = result.substring(0, i);
        }


        return result;
    }

    private String getInsertValueList() {
        String result = "";
        int cantCampos = 0;

        for (TableColumn col : tableColumnList) {

            cantCampos++;
            result += "p_" + col.getField() + "" + ", " + darSaltoLinea(cantCampos);

        }

        int i = result.lastIndexOf(',');
        if (i >= 0) {
            result = result.substring(0, i);
        }
        return result;
    }

    public int getPrimaryKeyCount() {
        int cont = 0;
        for (TableColumn tableColumn : tableColumnList) {
            if (tableColumn.isPrimaryKey()) {
                cont++;
            }
        }
        return cont;
    }

    private String getUpdateTableList() {
        int cantCampos = 0;
        String result = "";
        for (TableColumn col : tableColumnList) {
            if (!col.isPrimaryKey()) {
                cantCampos++;
                result += "`" + col.getField() + "`" + " = p_" + col.getField()
                        + ", " + darSaltoLinea(cantCampos);
            }

        }

        int i = result.lastIndexOf(',');
        if (i >= 0) {
            result = result.substring(0, i);
        }


        return result;
    }

    private String getWhereCondition() {
        String result = "";
        int nroClavesPrim = 0;
        for (TableColumn col : tableColumnList) {
            if (col.isPrimaryKey()) {
                nroClavesPrim += 1;
                if (nroClavesPrim > 1) {
                    result += " AND `" + col.getField() + "` = p_" + col.getField();
                } else {

                    result = "`" + col.getField() + "` = p_" + col.getField();
                }


            }
        }
        return result;
    }

    public String getInsertProcedureName(String tableName) {
        return procedurePrefix + insertProcName + tableName.replace(' ', '_');
    }

    public String getSelectProcedureName(String tableName) {
        return procedurePrefix + selectProcName + tableName.replace(' ', '_');
    }

    public String getUpdateProcedureName(String tableName) {
        return procedurePrefix + updateProcName + tableName.replace(' ', '_');
    }

    public String getDeleteProcedureName(String tableName) {
        return procedurePrefix + deleteProcName + tableName.replace(' ', '_');
    }

    public String getDetailsByIDProcedureName(String tableName) {
        return procedurePrefix + "_" + tableName.replace(' ', '_') + detailsProcName;
    }

    public String getInsert_StoredProcedure(DatabaseTable table) throws SQLException {
        fillTableColumnList(table);
        String tableName = table.getTableName();
        String proc = "DROP PROCEDURE IF EXISTS " + getInsertProcedureName(tableName) + ";\n";
        proc += "DELIMITER //\n";
        proc += "CREATE PROCEDURE " + getInsertProcedureName(tableName) + "(";
        proc += getStoredProcParameters() + "\n";
        proc += ")\n";
        proc += "BEGIN";
        proc += "\n";
        proc += "INSERT INTO `" + tableName + "`(" + getTableColumns() + ") \n";
        proc += "VALUES (" + getInsertValueList() + ");\r";
        proc += "\n";
        proc += "END //\n";
        proc += "DELIMITER ;\n";
        return proc;
    }

    public String getUpdate_StoredProcedure(DatabaseTable table) throws SQLException {
        String proc = "";

        if (getPrimaryKeyCount() >= 1) {
            if (getColumnsNonKey() == 0 && (tableColumnList.size() != getPrimaryKeyCount())) {
                proc = "#Imposible generar el procedimiento almacenado de UPDATE debido"
                        + " a que no hay más columna seleccionadas\n";

            } else {
                fillTableColumnList(table);
                String tableName = table.getTableName();
                proc = "DROP PROCEDURE IF EXISTS " + getUpdateProcedureName(tableName) + ";\n";
                proc += "DELIMITER //\n";
                proc += "CREATE PROCEDURE " + getUpdateProcedureName(tableName) + "(";
                proc += getStoredProcParameters() + "\n";
                proc += ")\n";
                proc += "BEGIN";
                proc += "\n";
                proc += "UPDATE `" + tableName + "` \n";
                proc += "SET " + getUpdateTableList() + "\n";
                proc += "WHERE " + getWhereCondition() + ";\n";
                proc += "END //\n";
                proc += "DELIMITER ;\n";
            }

        } else {
            proc = "#Imposible generar el procedimiento almacenado de UPDATE debido a"
                    + " que no hay claves primarias\n";
        }

        return proc;
    }

    public String getDelete_StoredProcedure(DatabaseTable table) throws SQLException {
        String proc = "";

        if (getPrimaryKeyCount() >= 1) {

            fillTableColumnList(table);
            String tableName = table.getTableName();
            proc = "DROP PROCEDURE IF EXISTS " + getDeleteProcedureName(tableName) + ";\n";
            proc += "DELIMITER //\n";
            proc += "CREATE PROCEDURE " + getDeleteProcedureName(tableName) + "(";
            proc += getPrimaryKeysAsStoredProcParams() + ")\n";
            proc += "BEGIN\n";
            proc += "DELETE FROM `" + tableName + "`\n";
            proc += "WHERE " + getWhereCondition() + ";\n";
            proc += "END //\n";
            proc += "DELIMITER ;\n";
        } else {
            proc = "#Imposible generar el procedimiento almacenado de DELETE debido a"
                    + " que no hay claves primarias\n";
        }

        return proc;
    }

    public String getSelectAll_StoredProcedure(DatabaseTable table) throws SQLException {
        fillTableColumnList(table);
        String tableName = table.getTableName();

        String proc = "DROP PROCEDURE IF EXISTS " + getSelectProcedureName(tableName) + ";\n";
        proc += "DELIMITER //\n";
        proc += "CREATE PROCEDURE " + getSelectProcedureName(tableName) + "()\n";
        proc += "BEGIN\n";
        proc += "SELECT " + getTableColumns() + "\n";
        proc += "FROM `" + tableName + "`;\n";
        proc += "END //\n";
        proc += "DELIMITER ;\n";
        return proc;
    }

    public String getGetDetailsById_StoredProcedure(DatabaseTable table) throws SQLException {
        String proc = "";
        if (getPrimaryKeyCount() >= 1) {
            if (getColumnsNonKey() == 0 && (tableColumnList.size() != getPrimaryKeyCount())) {

                proc = "#Imposible generar el procedimiento almacenado de UPDATE debido"
                        + " a que no hay más columna seleccionadas\n";

            } else {
                fillTableColumnList(table);
                String tableName = table.getTableName();
                proc = "DROP PROCEDURE IF EXISTS " + getDetailsByIDProcedureName(tableName) + ";\n";
                proc += "DELIMITER //\n";
                proc += "CREATE PROCEDURE " + getDetailsByIDProcedureName(tableName) + "(";
                proc += getPrimaryKeysAsStoredProcParams() + ")\n";
                proc += "BEGIN\n";
                proc += "SELECT " + getTableColumns() + "\n";
                proc += "FROM `" + tableName + "`\n";
                proc += "WHERE " + getWhereCondition() + ";\n";
                proc += "END //\n";
                proc += "DELIMITER ;\n";
            }
        } else {
            proc = "#Imposible generar el procedimiento almacenado de Detalles por código debido a"
                    + " que no hay claves primarias\n";
        }
        return proc;
    }

    public void setDeleteProcName(String deleteProcName) {
        this.deleteProcName = deleteProcName;
    }

    public void setInsertProcName(String insertProcName) {
        this.insertProcName = insertProcName;
    }

    public void setProcedurePrefix(String procedurePrefix) {
        this.procedurePrefix = procedurePrefix;
    }

    public void setSelectProcName(String selectProcName) {
        this.selectProcName = selectProcName;
    }

    public void setShortSyntax(boolean shortSyntax) {
        this.shortSyntax = shortSyntax;
    }

    public void setUpdateProcName(String updateProcName) {
        this.updateProcName = updateProcName;
    }

    public void setDetailsProcName(String detailsProcName) {
        this.detailsProcName = detailsProcName;
    }

    private int getColumnsNonKey() {
        int cant = 0;
        for (TableColumn col : tableColumnList) {
            if (col.isPrimaryKey() == false) {
                cant++;
            }
        }
        return cant;
    }
}
